﻿using DBUtil.MetaData;
using DBUtil.Provider.MySql.MetaData;
using DotNetCommon;
using DotNetCommon.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Index = DBUtil.MetaData.Index;

namespace DBUtil.Provider.MySql;

//内部不要传递表名, 全部传 ObjectName

public partial class MySqlManage : DBManage
{
    #region ShowSchemasAsync
    public override async Task<List<Schema>> ShowSchemasAsync()
    {
        var sql = $"select * from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME={db.GetCurrentDataBaseSqlSeg()}";
        var dt = await db.SelectDataTableAsync(sql);
        var res = new List<Schema>();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            res.Add(new MySqlShema()
            {
                Name = dt.Rows[i]["SCHEMA_NAME"].ToString(),
                DataBaseName = dt.Rows[i]["SCHEMA_NAME"].ToString(),
                DEFAULT_CHARACTER_SET_NAME = dt.Rows[i]["DEFAULT_CHARACTER_SET_NAME"].ToString(),
                DEFAULT_COLLATION_NAME = dt.Rows[i]["DEFAULT_COLLATION_NAME"].ToString()
            });
        }
        return res;
    }
    #endregion

    #region 表的元数据
    public async override Task<List<Table>> ShowTablesAsync(string schemaPureName = null) => await ShowTablesAsync(schemaPureName);
    private async Task<List<Table>> ShowTablesAsync(string schemaPureName = null, string tablePureName = null)
    {
        var tables = new List<Table>();
        var sql = $"select * from information_schema.TABLES t where t.TABLE_TYPE='BASE TABLE'";
        if (schemaPureName.IsNotNullOrWhiteSpace())
        {
            sql += $"\r\nand t.TABLE_SCHEMA={db.ProtectStringToSeg(schemaPureName)}";
        }
        else
        {
            sql += $"\r\nand t.TABLE_SCHEMA={db.GetCurrentSchemaSqlSeg()}";
        }
        if (tablePureName.IsNotNullOrWhiteSpace()) sql += $"\r\nand t.TABLE_NAME={db.ProtectStringToSeg(tablePureName)}";
        var dt = await db.SelectDataTableAsync(sql);
        for (int i = 0, len = dt.Rows.Count; i < len; i++) tables.Add(loadTableModel(dt.Rows[i]));
        return tables;
    }

    public override async Task<List<Table>> ShowCurrentSchemaTablesAsync() => await ShowTablesAsync();
    private MySqlTable loadTableModel(DataRow row)
    {
        var table = new MySqlTable()
        {
            Name = row["TABLE_NAME"].To<string>(),
            DataBaseName = row["TABLE_SCHEMA"].To<string>(),
            CreateTime = null,
            LastUpdate = null,
            Desc = row["TABLE_COMMENT"].To<string>(),
            Engine = row["ENGINE"].To<string>(),
            RowCount = row["TABLE_ROWS"].To<long>(),
            SchemaName = row["TABLE_SCHEMA"].To<string>(),

            INDEX_LENGTH = row["INDEX_LENGTH"].To<long>(),
            AVG_ROW_LENGTH = row["AVG_ROW_LENGTH"].To<long>(),
            DATA_LENGTH = row["DATA_LENGTH"].To<long>(),
            DATA_FREE = row["DATA_FREE"].To<long>(),
            MAX_DATA_LENGTH = row["MAX_DATA_LENGTH"].To<long>(),

            AUTO_INCREMENT = row["AUTO_INCREMENT"].To<long?>(),
            TABLE_COLLATION = row["TABLE_COLLATION"].To<string>(),
            CREATE_OPTIONS = row["CREATE_OPTIONS"].To<string>(),
            ROW_FORMAT = row["ROW_FORMAT"].To<string>(),
        };
        if (row["CREATE_TIME"].ToString().IsNotNullOrWhiteSpace()) table.CreateTime = row["CREATE_TIME"].To<DateTime>();
        if (row["UPDATE_TIME"].ToString().IsNotNullOrWhiteSpace()) table.LastUpdate = row["UPDATE_TIME"].To<DateTime>();
        return table;
    }

    public override async Task<Table> ShowTableDetailAsync(string tablePureName, string schemaPureName = null, EnumTableDetailType detailType = EnumTableDetailType.All)
    {
        var table = await GetTableSimpleInfoAsync(tablePureName, schemaPureName);
        if (table == null) return null;
        if ((detailType & EnumTableDetailType.All) > 0 || (detailType & EnumTableDetailType.Column) > 0)
        {
            var columns = table.Columns = await GetColumnsAsync(table.Name, table.SchemaName);
            table.PrimaryKey = columns.Where(i => i.IsPrimaryKey).ToStringSeparated(",");
        }
        if ((detailType & EnumTableDetailType.All) > 0 || (detailType & EnumTableDetailType.Trigger) > 0)
            table.Triggers = await GetTriggersAsync(table.Name, table.SchemaName);
        if ((detailType & EnumTableDetailType.All) > 0 || (detailType & EnumTableDetailType.Constraint) > 0)
            table.Constraints = await GetConstraintsAsync(table.Name, table.SchemaName);
        if ((detailType & EnumTableDetailType.All) > 0 || (detailType & EnumTableDetailType.Index) > 0)
            table.Indexes = await GetIndexesAsync(table.Name, table.SchemaName);
        return table;
    }
    private async Task<MySqlTable> GetTableSimpleInfoAsync(string tablePureName, string schemaPureName = null)
        => (await ShowTablesAsync(schemaPureName, tablePureName)).FirstOrDefault() as MySqlTable;

    private async Task<List<Column>> GetColumnsAsync(string tablePureName, string schemaPureName = null)
    {
        string sql = $@"select * from information_schema.COLUMNS T WHERE T.TABLE_NAME={db.ProtectStringToSeg(tablePureName)}";
        if (schemaPureName.IsNotNullOrWhiteSpace())
        {
            sql += $" and T.TABLE_SCHEMA={db.ProtectStringToSeg(schemaPureName)}";
        }
        else
        {
            sql += $" and T.TABLE_SCHEMA={db.GetCurrentSchemaSqlSeg()}";
        }
        sql += " order by T.ORDINAL_POSITION";
        DataTable dt = await db.SelectDataTableAsync(sql);
        var columns = new List<Column>();
        if (dt.Rows.Count > 0)
        {
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var column = new MySqlColumn();
                column.Name = dt.Rows[i]["COLUMN_NAME"].ToString();
                column.TableName = tablePureName;
                //列类型
                column.TypeString = dt.Rows[i]["COLUMN_TYPE"].ToString();
                column.DATA_TYPE = dt.Rows[i]["DATA_TYPE"].ToString();
                column.ConvertType();
                //TODO 设置类别
                //列说明
                column.Desc = dt.Rows[i]["COLUMN_COMMENT"].ToString();
                //字符排序规则
                column.CHARACTER_SET_NAME = dt.Rows[i]["CHARACTER_SET_NAME"].ToString();
                column.COLLATION_NAME = dt.Rows[i]["COLLATION_NAME"].ToString();
                column.CHARACTER_MAXIMUM_LENGTH = dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"].To<long?>();
                column.CHARACTER_OCTET_LENGTH = dt.Rows[i]["CHARACTER_OCTET_LENGTH"].To<long?>();
                column.NUMERIC_PRECISION = dt.Rows[i]["NUMERIC_PRECISION"].To<long?>();
                column.NUMERIC_SCALE = dt.Rows[i]["NUMERIC_SCALE"].To<long?>();
                column.DATETIME_PRECISION = dt.Rows[i]["DATETIME_PRECISION"].To<long?>();
                if (dt.Columns.Contains("SRS_ID")) column.SRS_ID = dt.Rows[i]["SRS_ID"].To<int?>();
                //是否可空
                column.IsNullAble = dt.Rows[i]["IS_NULLABLE"].ToString() == "NO" ? false : true;
                //自增
                var extra = dt.Rows[i]["EXTRA"].ToString();
                column.IsIdentity = extra == "auto_increment" ? true : false;
                if (column.IsIdentity)
                {
                    //先设置为1,1
                    column.IdentityStart = 1;
                    column.IdentityIncre = 1;
                }
                //虚列
                if (extra.Contains("VIRTUAL GENERATED"))
                {
                    column.IsComputed = true;
                    column.IsPersisted = false;
                    column.ComputedDefinition = dt.Rows[i]["GENERATION_EXPRESSION"].ToString();
                }
                else if (extra.Contains("STORED GENERATED"))
                {
                    column.IsComputed = true;
                    column.IsPersisted = true;
                    column.ComputedDefinition = dt.Rows[i]["GENERATION_EXPRESSION"].ToString();
                }

                //默认值
                var de = dt.Rows[i]["COLUMN_DEFAULT"].ToString();
                column.HasDefault = string.IsNullOrEmpty(de) ? false : true;
                if (column.HasDefault) column.Default = de;
                columns.Add(column);
                //主键 唯一
                var columnkey = dt.Rows[i]["COLUMN_KEY"].ToString();
                if (columnkey.Contains("PRI"))
                {
                    column.IsUnique = true;
                    column.IsPrimaryKey = true;
                }
                if (columnkey.Contains("UNI")) column.IsUnique = true;
            }
        }
        return columns;
    }
    private async Task<Column> GetColumnAsync(string tablePureName, string colPureName, string schemaPureName)
    {
        var cols = await GetColumnsAsync(tablePureName, schemaPureName);
        var column = cols.FirstOrDefault(col => col.Name.ToUpper() == colPureName?.ToUpper());
        return column;
    }

    private async Task<List<Trigger>> GetTriggersAsync(string tablePureName, string schemaPureName = null)
    {
        var triggers = new List<Trigger>();
        string sql = $"select * from information_schema.`TRIGGERS` where TRIGGER_SCHEMA={(schemaPureName.IsNotNullOrWhiteSpace() ? db.ProtectStringToSeg(schemaPureName) : db.GetCurrentSchemaSqlSeg())} and EVENT_OBJECT_TABLE={db.ProtectStringToSeg(tablePureName)}";
        var dt = await db.SelectDataTableAsync(sql);
        for (int i = 0, len = dt.Rows.Count; i < len; i++)
        {
            string name = dt.Rows[i]["TRIGGER_NAME"].ToString();
            var condition = dt.Rows[i]["EVENT_MANIPULATION"].ToString().ToUpper();
            bool isUpdate = condition == "UPDATE";
            bool isDelete = condition == "DELETE";
            bool isInsert = condition == "INSERT";
            bool isAfter = dt.Rows[i]["ACTION_TIMING"].ToString() == "AFTER" ? false : true;
            var createtime = DateTime.Parse(dt.Rows[i]["CREATED"].ToString());
            Trigger tri = new MySqlTrigger()
            {
                Name = name,
                TableName = tablePureName,
                IsInsert = isInsert,
                IsUpdate = isUpdate,
                IsDelete = isDelete,
                IsAfter = isAfter,
                CreateTime = createtime,
            };
            triggers.Add(tri);
        }
        if (triggers.IsNotNullOrEmpty())
        {
            sql = triggers.Select(i => $"show create trigger {i.Name}").ToStringSeparated(";");
            var ds = db.SelectDataSet(sql);
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                var dt2 = ds.Tables[i];
                string name = dt2.Rows[0]["Trigger"].ToString();
                string createSql = dt2.Rows[0]["SQL Original Statement"].ToString();
                triggers.FirstOrDefault(i => i.Name == name).CreateSql = createSql;
            }
        }
        return triggers;
    }

    private async Task<List<DBUtil.MetaData.Constraint>> GetConstraintsAsync(string tablePureName, string schemaPureName = null)
    {
        var sql = $@"
SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.CONSTRAINT_TYPE,
    t.CONSTRAINT_NAME,
    k.COLUMN_NAME,
    k.REFERENCED_TABLE_SCHEMA,
    k.REFERENCED_TABLE_NAME,
    k.REFERENCED_COLUMN_NAME
FROM
    information_schema.TABLE_CONSTRAINTS t inner join
  information_schema.KEY_COLUMN_USAGE as k ON (t.TABLE_SCHEMA = k.TABLE_SCHEMA     AND t.TABLE_NAME = k.TABLE_NAME    AND t.CONSTRAINT_NAME = k.CONSTRAINT_NAME )
WHERE
    t.TABLE_SCHEMA = {(schemaPureName.IsNotNullOrWhiteSpace() ? db.ProtectStringToSeg(schemaPureName) : db.GetCurrentSchemaSqlSeg())}
     AND t.table_name IN ({db.ProtectStringToSeg(tablePureName)})
ORDER BY
    t.table_name,
    t.constraint_name;
    -- t.ORDINAL_POSITION -- 并不是所有版本都有这一列;
";
        var constraints = new List<DBUtil.MetaData.Constraint>();
        var dt = await db.SelectDataTableAsync(sql);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var constraint = new MySqlConstraint()
            {
                TableName = dt.Rows[i]["TABLE_NAME"].ToString(),
                SchemaName = dt.Rows[i]["TABLE_SCHEMA"].ToString(),
                Name = dt.Rows[i]["CONSTRAINT_NAME"].ToString(),
                ColumnName = dt.Rows[i]["COLUMN_NAME"].ToString(),
                ReferenceTableName = dt.Rows[i]["REFERENCED_TABLE_NAME"].To<string>(),
                ReferenceColumnName = dt.Rows[i]["REFERENCED_COLUMN_NAME"].To<string>(),
            };
            string type = (dt.Rows[i]["CONSTRAINT_TYPE"] ?? "").ToString();
            if (type.StartsWith("PRIMARY"))
            {
                constraint.Type = EnumConstraintType.PrimaryKey;
            }
            else if (type.StartsWith("UNIQUE"))
            {
                constraint.Type = EnumConstraintType.Unique;
            }
            else if (type.StartsWith("FOREIGN KEY"))
            {
                constraint.Type = EnumConstraintType.ForeignKey;
            }
            constraints.Add(constraint);
        }

        try
        {
            //Unknown table 'check_constraints' in information_schema 低版本mysql不支持 mysql 8.0.16 及以上开始支持检查约束
            sql = $@"
SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.CONSTRAINT_TYPE,
    t.CONSTRAINT_NAME,
    c.CHECK_CLAUSE
FROM
    information_schema.TABLE_CONSTRAINTS t left join information_schema.CHECK_CONSTRAINTS c on t.TABLE_SCHEMA=c.CONSTRAINT_SCHEMA and t.CONSTRAINT_NAME=c.CONSTRAINT_NAME
WHERE
    t.TABLE_SCHEMA = {(schemaPureName.IsNotNullOrWhiteSpace() ? db.ProtectStringToSeg(schemaPureName) : db.GetCurrentSchemaSqlSeg())}
     AND t.table_name IN ({db.ProtectStringToSeg(tablePureName)})
     and t.CONSTRAINT_TYPE='CHECK'
ORDER BY
    t.table_name,
    t.constraint_name;
";
            dt = await db.SelectDataTableAsync(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var constraint = new MySqlConstraint()

                {
                    TableName = dt.Rows[i]["TABLE_NAME"].ToString(),
                    SchemaName = dt.Rows[i]["TABLE_SCHEMA"].ToString(),
                    Name = dt.Rows[i]["CONSTRAINT_NAME"].ToString(),
                    Type = EnumConstraintType.Check,
                    Content = dt.Rows[i]["CHECK_CLAUSE"].ToString()
                };
                constraints.Add(constraint);
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        var fks = constraints.Where(i => i.Type == EnumConstraintType.ForeignKey).Select(i => $"{db.ProtectStringToSeg(i.Name)}").Distinct().ToList();
        if (fks.IsNotNullOrEmpty())
        {
            //外键补充
            try
            {
                sql = $@"select CONSTRAINT_NAME,UPDATE_RULE,DELETE_RULE from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME in({fks.ToStringSeparated(",")}) and CONSTRAINT_SCHEMA={(schemaPureName.IsNotNullOrWhiteSpace() ? db.ProtectStringToSeg(schemaPureName) : db.GetCurrentSchemaSqlSeg())};";
                dt = await db.SelectDataTableAsync(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var fkName = dt.Rows[i]["CONSTRAINT_NAME"].ToString();
                    var fks2 = constraints.Where(i => i.Name == fkName).ToList();
                    fks2.ForEach(fk =>
                    {
                        fk.Delete_Action = dt.Rows[i]["DELETE_RULE"].ToString();
                        fk.Update_Action = dt.Rows[i]["UPDATE_RULE"].ToString();
                    });
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        return constraints;
    }

    private async Task<List<Index>> GetIndexesAsync(string tablePureName, string schemaPureName = null)
    {
        var indexs = new List<Index>();
        if (schemaPureName.IsNullOrWhiteSpace()) schemaPureName = await db.SchemaNameAsync;
        var schemaNameQuoted = db.AddQuote(schemaPureName);
        var sql = $"show index from {schemaNameQuoted}.{db.AddQuote(tablePureName)};";
        var dt = await db.SelectDataTableAsync(sql);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var index = new MySqlIndex
            {
                DataBaseName = schemaPureName,
                TableName = tablePureName,
                SchemaName = schemaPureName,
                CreateTime = null,
                IsClustered = dt.Rows[i]["Key_name"].ToString() == "PRIMARY",
                Desc = dt.Rows[i]["Comment"].ToString().IfNullOrEmptyUse(dt.Rows[i]["Index_comment"].ToString()),
                IndexType = dt.Rows[i]["Index_type"].ToString(),
                IsNullAble = dt.Rows[i]["Null"].To<bool>(),
                IsUnique = !dt.Rows[i]["Non_unique"].To<bool>(),
                LastUpdate = null,
                Name = dt.Rows[i]["Key_name"].ToString(),
                ColumnName = dt.Rows[i]["Column_name"].ToString(),
                Seq_in_index = dt.Rows[i]["Seq_in_index"].To<int>(),
                Expression = dt.Rows[i]["Expression"]?.ToString(),
                SubPart = dt.Columns.Contains("SubPart") ? dt.Rows[i]["SubPart"]?.To<int>() : null,//看版本
            };
            indexs.Add(index);
        }
        return indexs;
    }

    public override async Task<List<DBUtil.MetaData.Constraint>> ShowTableConstraintsAsync(string tablePureName, string schemaPureName = null)
    {
        return await GetConstraintsAsync(tablePureName, schemaPureName);
    }

    public override async Task<List<Index>> ShowTableIndexesAsync(string tablePureName, string schemaPureName = null)
    {
        return await GetIndexesAsync(tablePureName, schemaPureName);
    }

    public override async Task<List<Column>> ShowTableColumnsAsync(string tablePureName, string schemaPureName = null)
    {
        return await GetColumnsAsync(tablePureName, schemaPureName);
    }

    public override async Task<List<Trigger>> ShowTableTriggersAsync(string tablePureName, string schemaPureName = null)
    {
        return await GetTriggersAsync(tablePureName, schemaPureName);
    }
    #endregion

    #region 视图元数据
    /// <inheritdoc />
    public override async Task<List<View>> ShowViewsAsync(string schemaPureName = null) => await ShowViewsAsync(schemaPureName);

    private async Task<List<View>> ShowViewsAsync(string viewPureName = null, string schemaPureName = null)
    {
        var views = new List<View>();
        var sql = $@"
select t.table_schema,t.TABLE_NAME,t.CREATE_TIME,t.UPDATE_TIME,v.definer,v.VIEW_DEFINITION,v. CHARACTER_SET_CLIENT,v.COLLATION_CONNECTION,v.SECURITY_TYPE,v.CHECK_OPTION
from information_schema.`TABLES` t 
left join information_schema.VIEWS v on v.TABLE_SCHEMA=t.TABLE_SCHEMA and v.TABLE_NAME=t.TABLE_NAME
where
    t.TABLE_TYPE='VIEW'";
        if (schemaPureName.IsNotNullOrWhiteSpace())
        {
            sql += $"\r\nand t.table_schema={db.ProtectStringToSeg(schemaPureName)}";
        }
        else
        {
            sql += $"\r\nand t.TABLE_SCHEMA={db.GetCurrentSchemaSqlSeg()}";
        }
        if (viewPureName.IsNotNullOrWhiteSpace())
        {
            sql += $"\r\nand t.TABLE_NAME={db.ProtectStringToSeg(viewPureName)}";
        }
        var dt = await db.SelectDataTableAsync(sql);
        for (int i = 0, len = dt.Rows.Count; i < len; i++) views.Add(loadViewModel(dt.Rows[i]));
        return views;
    }

    private View loadViewModel(DataRow row)
    {
        var name = row["TABLE_NAME"].ToString();
        var schema = row["TABLE_SCHEMA"].ToString();
        var view_definition = row["VIEW_DEFINITION"].ToString();
        var view = new MySqlView()
        {
            Name = name,
            SchemaName = schema,
            VIEW_DEFINITION = view_definition,
            DEFINER = row["DEFINER"].ToString(),
            SECURITY_TYPE = row["SECURITY_TYPE"].ToString(),
            CHECK_OPTION = row["CHECK_OPTION"].ToString(),
            CHARACTER_SET_CLIENT = row["CHARACTER_SET_CLIENT"].To<string>(),
            COLLATION_CONNECTION = row["COLLATION_CONNECTION"].To<string>(),
            CreateTime = row["CREATE_TIME"].To<DateTime?>(),
            LastUpdate = row["UPDATE_TIME"].To<DateTime?>(),
        };
        view.CreateSql = $"create view `{schema}`.`{name}` as\r\n{view_definition}";
        return view;
    }
    /// <inheritdoc />
    public override async Task<List<View>> ShowCurrentSchemaViewsAsync() => await ShowViewsAsync();
    public override async Task<View> ShowViewDetailAsync(string viewPureName, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(viewPureName);
        var view = await GetViewSimpleInfoAsync(viewPureName, schemaPureName);
        if (view == null) return null;
        var cols = await GetColumnsAsync(viewPureName, schemaPureName);
        view.Columns = cols;
        return view;
    }
    private async Task<MySqlView> GetViewSimpleInfoAsync(string viewPureName, string schemaPureName = null)
        => (await ShowViewsAsync(viewPureName, schemaPureName)).FirstOrDefault() as MySqlView;
    #endregion

    #region 存储过程&函数&序列 元数据 todo: 这种接口设计 要考虑schema的隔离
    //todo 最好是:
    //ShowProcedures(string schemaName = null) 一个
    //ShowCurrentProcedures() 一个
    //只不过对mysql都必须是当前schema的
    public override async Task<List<Procedure>> ShowProceduresAsync(string schemaPureName = null)
    {
        var res = new List<Procedure>();
        string sql = $@"select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'";
        if (schemaPureName.IsNotNullOrWhiteSpace())
        {
            sql += $" and ROUTINE_SCHEMA={db.ProtectStringToSeg(schemaPureName)}";
        }
        else
        {
            sql += $" and ROUTINE_SCHEMA={db.GetCurrentSchemaSqlSeg()}";
        }
        var dt = await db.SelectDataTableAsync(sql);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var proc = new MySqlProcedure();
            proc.Name = dt.Rows[i]["ROUTINE_NAME"].ToString();
            proc.CreateTime = DateTime.Parse(dt.Rows[i]["CREATED"].ToString());
            var lastupdate = dt.Rows[i]["LAST_ALTERED"].ToString();
            if (!string.IsNullOrWhiteSpace(lastupdate))
            {
                proc.LastUpdate = DateTime.Parse(lastupdate);
            }
            proc.SchemaName = db.DBName;
            res.Add(proc);
        }
        if (res.IsNotNullOrEmpty())
        {
            var sql2 = res.Select(i => $"show create PROCEDURE {db.AddQuote(i.Name)}").ToStringSeparated(";");
            var ds = await db.SelectDataSetAsync(sql);
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                var dt2 = ds.Tables[i];
                var name = dt2.Rows[0]["Procedure"].ToString();
                var createSql = dt2.Rows[0]["Create Procedure"].ToString();
                res.FirstOrDefault(i => i.Name == name).CreateSql = createSql;
            }
        }
        return res;
    }

    public override async Task<List<Function>> ShowFunctionsAsync(string schemaPureName = null)
    {
        var sql = @"select r.ROUTINE_SCHEMA,r.ROUTINE_NAME,r.ROUTINE_TYPE,r.DATA_TYPE,r.created,r.LAST_ALTERED,r.`DEFINER`
from information_schema.ROUTINES r
where routine_type='FUNCTION'";
        if (schemaPureName.IsNullOrWhiteSpace()) sql += $" and routine_schema={db.GetCurrentSchemaSqlSeg()}";
        else sql += $" and routine_schema={db.ProtectStringToSeg(schemaPureName)}";
        var dt = await db.SelectDataTableAsync(sql);
        var list = new List<Function>();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var row = dt.Rows[i];
            var func = new Function()
            {
                Name = row["ROUTINE_NAME"].ToString(),
                DataBaseName = row["ROUTINE_SCHEMA"].ToString(),
                SchemaName = row["ROUTINE_SCHEMA"].ToString(),
                CreateTime = row["CREATED"]?.ToString().To<DateTime?>(),
                LastUpdate = row["LAST_ALTERED"]?.ToString().To<DateTime?>()
            };
            list.Add(func);
        }
        if (list.Count > 0)
        {
            sql = list.Select(i => $"show create function {db.AddQuote(i.Name)}").ToStringSeparated(";");
            var ds = await db.SelectDataSetAsync(sql);
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                var dt2 = ds.Tables[i];
                var name = dt.Rows[0]["Function"].ToString();
                var createSql = dt.Rows[0]["Create Function"].ToString();
                list.FirstOrDefault(i => i.Name == name).CreateSql = createSql;
            }
        }
        return list;
    }

    public override Task<List<Sequence>> ShowSequencesAsync(string schemaName = null)
        //mysql不支持序列
        => Task.FromResult(new List<Sequence>());
    #endregion
}
